﻿CREATE PROCEDURE [dbo].[utl_CreateSPDel]
@Table [sysname], @ParentObject [sysname]=NULL
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @ID                   INT
	DECLARE @SQL                  NVARCHAR(MAX)
	DECLARE @ColumnSelectList     VARCHAR(MAX)
	DECLARE @ColumnUpdateList     VARCHAR(MAX)
	DECLARE @ColumnVariablesList  VARCHAR(MAX)
	DECLARE @WhereClause          VARCHAR(MAX)
	DECLARE @WhereClause2         VARCHAR(MAX)
	DECLARE @ProcedureName        sysname
	DECLARE @ColumnName           sysname
	DECLARE @Datatype             sysname
	DECLARE @QuotedColumnName     sysname
	DECLARE @VariableColumnName   sysname
	DECLARE @Schema				  sysname
	DECLARE @IsIdentity           BIT
	DECLARE @IsTimestamp          BIT
	DECLARE @IsComputed           BIT
	DECLARE @IsNullable           BIT
	DECLARE @IsForeignKey		  BIT
	DECLARE @NumericPrecision     SMALLINT
	DECLARE @NumericScale         SMALLINT
	DECLARE @MaxLength            INT

	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0

	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)
	DECLARE @TAB  VARCHAR(1); SET @TAB = CHAR(9)

	DECLARE @INDENT INT; SET @INDENT = 4

	DECLARE @Out TABLE (
				ID INTEGER IDENTITY(1, 1) PRIMARY KEY, 
				Line VARCHAR(MAX)
			)

	DECLARE @Columns  TABLE (
				ID INTEGER IDENTITY(1, 1) PRIMARY KEY,
				ColumnName sysname,
				QuotedColumnName sysname,
				VariableColumnName sysname,
				Datatype sysname,
				MaxLength INT,
				IsIdentity BIT,
				IsTimestamp BIT,
				IsComputed BIT,
				IsPartOfPrimaryKey BIT,
				IsNullable BIT,
				NumericPrecision SMALLINT,
				NumericScale SMALLINT,
				IsForeignKey BIT
			)

	-- make sure table exists    
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not exist.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 1
	END

	-- make sure table has a primary key
	IF dbo.fn_TableHasPrimaryKey(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not have a primary key.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 2
	END

	-- make sure table has a timestamp column
	IF dbo.fn_TableHasTimestamp(@Table) = @False
	BEGIN
		SET @SQL = 'Table ''%s'' does not have a timestamp column.'
		RAISERROR(@SQL, 16, 1, @Table)
		RETURN 3
	END

	IF @ParentObject IS NULL
	BEGIN
		EXECUTE [dbo].[utl_CreateSPScriptTable]
		SET @ParentObject = OBJECT_NAME(@@PROCID)
	END

	-- build name of stored procedure to create
	SET @Schema = ISNULL(PARSENAME(@Table, 2), 'dbo')
	SET @ProcedureName = @Schema +  '.usp_' + @Table + '_Del'

	SET @SQL = 'DECLARE @True  BIT; SET @True  = 1'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @False BIT; SET @False = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- force any existing procedure to be deleted
	SET @SQL = 'IF dbo.fn_ProcedureExists(''' + @ProcedureName + ''') = @True'
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = SPACE(@INDENT) + 'DROP PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName)
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- create a table of column names and certain attributes
	INSERT INTO @Columns
	  (
		ColumnName,
		QuotedColumnName,
		VariableColumnName,
		Datatype,
		MaxLength,
		IsIdentity,
		IsTimestamp,
		IsComputed,
		IsPartOfPrimaryKey,
		IsNullable,
		NumericPrecision,
		NumericScale,
		IsForeignKey
	  )
	SELECT ColumnName,
		   QuotedColumnName,
		   VariableColumnName,
		   Datatype,
		   MaxLength,
		   IsIdentity,
		   IsTimestamp,
		   IsComputed,
		   IsPartOfPrimaryKey,
		   IsNullable,
		   NumericPrecision,
		   NumericScale,
		   0
	FROM   dbo.fn_ColumnsAndAttributes(@Table)

	--------------------------------------------------------------------------------
	-- identify foreign keys
	IF EXISTS (
		   SELECT 1
		   WHERE  (OBJECT_ID('tempdb..#sp_fkeys_results') IS NOT NULL)
	   )
		DROP TABLE #sp_fkeys_results

	CREATE TABLE #sp_fkeys_results
	(
		PKTABLE_QUALIFIER  sysname,
		PKTABLE_OWNER      sysname,
		PKTABLE_NAME       sysname,
		PKCOLUMN_NAME      sysname,
		FKTABLE_QUALIFIER  sysname,
		FKTABLE_OWNER      sysname,
		FKTABLE_NAME       sysname,
		FKCOLUMN_NAME      sysname,
		KEY_SEQ            SMALLINT,
		UPDATE_RULE        SMALLINT,
		DELETE_RULE        SMALLINT,
		FK_NAME            sysname,
		PK_NAME            sysname,
		DEFERRABILITY      SMALLINT
	)

	INSERT INTO #sp_fkeys_results
	EXECUTE sp_fkeys @fktable_name = @Table

	IF (
		   SELECT COUNT(*)
		   FROM   #sp_fkeys_results
	   ) > 0

	BEGIN
		DECLARE @FKeyColumns TABLE (ID INT IDENTITY PRIMARY KEY, FKeyColumn sysname)

		INSERT INTO @FKeyColumns
		  (
			FKeyColumn
		  )
		SELECT FKCOLUMN_NAME
		FROM   #sp_fkeys_results

		IF EXISTS (
			   SELECT 1
			   WHERE  (OBJECT_ID('tempdb..#sp_fkeys_results') IS NOT NULL)
		   )
			DROP TABLE #sp_fkeys_results

		UPDATE @Columns
		SET    IsForeignKey = @True
		WHERE  EXISTS (
				   SELECT *
				   FROM   @FKeyColumns FC
				   WHERE  FC.FKeyColumn = ColumnName
			   )
	END
	
	--------------------------------------------------------------------------------

	-- build proc name
	SET @SQL = 'CREATE PROCEDURE ' + dbo.fn_QuoteName(@ProcedureName) + ' ('
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- build list of column names for parameters
	
	SELECT @ID = MIN(ID)
	FROM (			   
		SELECT TOP 100 PERCENT ID
		FROM   @Columns
		WHERE  ((IsPartOfPrimaryKey = 1) OR (IsTimestamp = 1) OR (IsForeignKey = 1))
		ORDER BY (
			CASE WHEN IsPartOfPrimaryKey = @True THEN 1
				 WHEN IsForeignKey = @True THEN 2
				 WHEN IsTimeStamp = @True THEN 3
				 ELSE 4
			END
		)
	) A
	
	WHILE @ID IS NOT NULL
	BEGIN
		SELECT @ColumnName = ColumnName,
			   @QuotedColumnName = QuotedColumnName,
			   @VariableColumnName = VariableColumnName,
			   @Datatype = Datatype,
			   @MaxLength = MaxLength,
			   @IsIdentity = IsIdentity,
			   @IsTimestamp = IsTimestamp,
			   @IsComputed = IsComputed,
			   @IsNullable = IsNullable,
			   @NumericPrecision = NumericPrecision,
			   @NumericScale = NumericScale,
			   @IsForeignKey = IsForeignKey
		FROM   @Columns
		WHERE  ID = @ID
		-- build list of parameters with datatypes and lengths
		SET @SQL = SPACE(@INDENT) + @VariableColumnName
		SET @SQL = @SQL + ' ' + UPPER(@Datatype)
		IF @Datatype IN ('VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR')
			SET @SQL = @SQL + '(' + CAST(@MaxLength AS VARCHAR(4)) + ')'
	    
		IF @Datatype IN ('NUMERIC', 'DECIMAL')
			SET @SQL = @SQL + '(' + CAST(@NumericPrecision AS VARCHAR(2)) + ',' + 
				CAST(@NumericScale AS VARCHAR(2)) + ')'
		
		IF @IsForeignKey = @True
			SET @SQL = @SQL + '=NULL'
	    
		SELECT @ID = MIN(ID)
		FROM (			
			SELECT TOP 100 PERCENT ID
			FROM   @Columns
			WHERE  ID > @ID
			AND ((IsPartOfPrimaryKey = 1) OR (IsTimestamp = 1) OR (IsForeignKey = 1))
			ORDER BY (
				CASE WHEN IsPartOfPrimaryKey = @True THEN 1
					 WHEN IsForeignKey = @True THEN 2
					 WHEN IsTimeStamp = @True THEN 3
					 ELSE 4
				END
			)
		) A
		
		IF @@ROWCOUNT = 0
			select @id = null
		
		IF @ID IS NULL
			SET @SQL = @SQL + ')'
		ELSE
			SET @SQL = @SQL + ','
	    
		INSERT INTO @Out (Line) VALUES (@SQL)
	END

	SET @SQL = 'AS'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'BEGIN'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'DECLARE @ErrorCode AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'DECLARE @RowsAffected AS INT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'DELETE FROM ' + dbo.fn_QuoteName(@Table) + ' WITH (ROWLOCK)'
	INSERT INTO @Out (Line) VALUES (@SQL)

	-- build where clause based on primary key columns
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsPartOfPrimaryKey = @True

	WHILE @ID IS NOT NULL
	BEGIN
		SET @WhereClause = ISNULL(@WhereClause + ' AND ', '') + '(' + (
				SELECT QuotedColumnName
				FROM   @Columns
				WHERE  ID = @ID
			) + ' = ' + (
				SELECT VariableColumnName
				FROM   @Columns
				WHERE  ID = @ID
			) + ')'
	    
		SELECT @ID = MIN(ID)
		FROM   @Columns
		WHERE  ID > @ID
			   AND IsPartOfPrimaryKey = @True
	END
	
	-- add concurrency check to where clause
	SET @WhereClause = @WhereClause + ' AND ' + '(' + dbo.fn_QuoteName(dbo.fn_TimestampColumn(@Table)) 
		+ ' = ' + (
			SELECT VariableColumnName
			FROM   @Columns
			WHERE  IsTimestamp = @True
		) + ')'

	SET @SQL = SPACE(@INDENT) + 'WHERE ' + @WhereClause
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = SPACE(@INDENT) + 'OPTION (FAST 1)'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'SELECT @ErrorCode = @@ERROR, @RowsAffected = @@ROWCOUNT'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'IF @RowsAffected = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = SPACE(@INDENT) + 'RETURN 50001  -- concurrency violation'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'RETURN @ErrorCode'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'END'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = REPLICATE('-', 80)
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	  
	INSERT INTO CreateSPScriptTable
	  (
		Line
	  )
	SELECT Line
	FROM   @Out
	ORDER BY
		   ID

	IF @ParentObject = OBJECT_NAME(@@PROCID)
	BEGIN
		SELECT Line
		FROM   CreateSPScriptTable
		ORDER BY
			   ID
	END
	
	SET NOCOUNT OFF

	RETURN 0

END

